--    Program: 		CAPPOEXT_JOB.sql
--    Description:	Extract PO Job information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
  l_count				   number;
  l_processed_count_1 	   number;
  l_rejected_count_1  	   number;
  l_processed_count_2	   number;
  l_rejected_count_2	   number;
  l_err_msg       		   varchar2(2000);
                    		
  l_utl_file_1  	       utl_file.file_type;
  l_utl_file_2 		       utl_file.file_type;
  l_filename_1 		       varchar2(255);
  l_filename_2 		       varchar2(255);
  l_pathname  		       varchar2(100);
                           
  l_ngo_sob			       varchar2(20);

  l_set_of_books_id		   number;
 
  cursor cur_PER_JOBS(P_SET_OF_BOOKS_NAME varchar2) is
	select pj.*
	from   PER_JOBS pj, HR_ORGANIZATION_UNITS hou
    where  pj.business_group_id = hou.business_group_id
    AND    upper(hou.name) = 'BG_'||upper(substr(P_SET_OF_BOOKS_NAME, -4));
                                 
  cursor cur_PER_JOBS_TL(P_JOB_ID number) is  
    select *
    from   PER_JOBS_TL 
    where  JOB_ID = P_JOB_ID;
  
begin
  l_pathname := '/tmp';
  l_filename_1 := 'CAPPO_JOB_B.dat';
  l_filename_2 := 'CAPPO_JOB_TL.dat';
  l_utl_file_1 := utl_file.fopen(l_pathname, l_filename_1, 'w');
  l_utl_file_2 := utl_file.fopen(l_pathname, l_filename_2, 'w');
  
  l_count := 0;
  l_processed_count_1 := 0;
  l_rejected_count_1  := 0;
  l_processed_count_2 := 0;
  l_rejected_count_2  := 0;  
                                         
  l_ngo_sob := '&1';                                                             

  begin
    select set_of_books_id
    into   l_set_of_books_id
    from   gl_sets_of_books
    where  upper(short_name) = upper(l_ngo_sob);     
  exception
     when others then
       dbms_output.put_line(l_ngo_sob||': set of books name not found');
  end;  

   for f in cur_PER_JOBS(l_ngo_sob) loop
          
     utl_file.put_line(l_utl_file_1,
       f.JOB_ID                    || chr(9) ||
       f.BUSINESS_GROUP_ID         || chr(9) ||
       f.JOB_DEFINITION_ID         || chr(9) ||
       f.DATE_FROM                 || chr(9) ||
       f.COMMENTS                  || chr(9) ||
       f.DATE_TO                   || chr(9) ||
       f.NAME                      || chr(9) ||
       f.REQUEST_ID                || chr(9) ||
       f.PROGRAM_APPLICATION_ID    || chr(9) ||
       f.PROGRAM_ID                || chr(9) ||
       f.PROGRAM_UPDATE_DATE       || chr(9) ||
       f.ATTRIBUTE_CATEGORY        || chr(9) ||
       f.ATTRIBUTE1                || chr(9) ||
       f.ATTRIBUTE2                || chr(9) ||
       f.ATTRIBUTE3                || chr(9) ||
       f.ATTRIBUTE4                || chr(9) ||
       f.ATTRIBUTE5                || chr(9) ||
       f.ATTRIBUTE6                || chr(9) ||
       f.ATTRIBUTE7                || chr(9) ||
       f.ATTRIBUTE8                || chr(9) ||
       f.ATTRIBUTE9                || chr(9) ||
       f.ATTRIBUTE10               || chr(9) ||
       f.ATTRIBUTE11               || chr(9) ||
       f.ATTRIBUTE12               || chr(9) ||
       f.ATTRIBUTE13               || chr(9) ||
       f.ATTRIBUTE14               || chr(9) ||
       f.ATTRIBUTE15               || chr(9) ||
       f.ATTRIBUTE16               || chr(9) ||
       f.ATTRIBUTE17               || chr(9) ||
       f.ATTRIBUTE18               || chr(9) ||
       f.ATTRIBUTE19               || chr(9) ||
       f.ATTRIBUTE20               || chr(9) ||
       f.LAST_UPDATE_DATE          || chr(9) ||
       f.LAST_UPDATED_BY           || chr(9) ||
       f.LAST_UPDATE_LOGIN         || chr(9) ||
       f.CREATED_BY                || chr(9) ||
       f.CREATION_DATE             || chr(9) ||
       f.JOB_INFORMATION_CATEGORY  || chr(9) ||
       f.JOB_INFORMATION1          || chr(9) ||
       f.JOB_INFORMATION2          || chr(9) ||
       f.JOB_INFORMATION3          || chr(9) ||
       f.JOB_INFORMATION4          || chr(9) ||
       f.JOB_INFORMATION5          || chr(9) ||
       f.JOB_INFORMATION6          || chr(9) ||
       f.JOB_INFORMATION7          || chr(9) ||
       f.JOB_INFORMATION8          || chr(9) ||
       f.JOB_INFORMATION9          || chr(9) ||
       f.JOB_INFORMATION10         || chr(9) ||
       f.JOB_INFORMATION11         || chr(9) ||
       f.JOB_INFORMATION12         || chr(9) ||
       f.JOB_INFORMATION13         || chr(9) ||
       f.JOB_INFORMATION14         || chr(9) ||
       f.JOB_INFORMATION15         || chr(9) ||
       f.OBJECT_VERSION_NUMBER     || chr(9) ||
       f.JOB_INFORMATION16         || chr(9) ||
       f.JOB_INFORMATION17         || chr(9) ||
       f.JOB_INFORMATION18         || chr(9) ||
       f.JOB_INFORMATION19         || chr(9) ||
       f.JOB_INFORMATION20         || chr(9) ||
       f.BENCHMARK_JOB_FLAG        || chr(9) ||
       f.BENCHMARK_JOB_ID          || chr(9) ||
       f.EMP_RIGHTS_FLAG           || chr(9) ||
       f.JOB_GROUP_ID              || chr(9) ||
       f.APPROVAL_AUTHORITY 
     );
   
     for s in cur_PER_JOBS_TL(f.job_id) loop
        utl_file.put_line(l_utl_file_2,
        s.job_id || chr(9) ||            
        s.LANGUAGE || chr(9) ||         
        s.source_lang || chr(9) ||      
        s.name || chr(9) ||            
        s.created_by || chr(9) ||      
        s.creation_date || chr(9) ||   
        s.last_updated_by || chr(9) || 
        s.last_update_date || chr(9) ||
        s.last_update_login
        );
        
        l_processed_count_2 := l_processed_count_2 + 1;     
     end loop;
     
     l_processed_count_1 := l_processed_count_1 + 1;        
   end loop;
   
   l_err_msg := '';
   if l_processed_count_1 = 0 then   
     l_rejected_count_1 := l_rejected_count_1 + 1;
     l_err_msg := l_ngo_sob || ': Job not found';
   end if; 
      
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ( 'CAPPOEXT_JOB_B', sysdate, l_processed_count_1, l_rejected_count_1, l_processed_count_1-l_rejected_count_1, l_err_msg );            
   
   l_err_msg := '';
   if l_processed_count_2 = 0 then   
     l_rejected_count_2 := l_rejected_count_2 + 1;
     l_err_msg := l_ngo_sob || ': Job not found';
   end if;      
           
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ( 'CAPPOEXT_JOB_TL', sysdate, l_processed_count_2, l_rejected_count_2, l_processed_count_2-l_rejected_count_2, l_err_msg );      
      
   commit;
   
   utl_file.fclose(l_utl_file_1);
   utl_file.fclose(l_utl_file_2);
exception 
   when others then
     dbms_output.put_line(sqlerrm);
     rollback;
end;
/


--exit;
